﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Xml;
using DataAccess;
using DataAccess.Entity;

namespace SkynetMSSQLAccess
{

    public class MsSqlDataAccess : AbstractDataAccess
    {
        #region Constructor
        /// <summary>
        /// 默认构造函数
        /// </summary>
        public MsSqlDataAccess()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="conn">MsSql数据连接对象</param>
        public MsSqlDataAccess(SqlConnection conn)
        {
            this.m_DbConnection = conn;
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public MsSqlDataAccess(string connectionString)
        {
            this.m_DbConnection = new SqlConnection(connectionString);
        }

        #endregion

        #region IDataAccess 成员
        //重载父类关闭
        //连接池中重复调用时 当调用过于频繁 会造成 在执行SQL语句时 报出 内存不可写 的错误
        //目前处理方法是 当每次关闭时 重新实例化一个连接
        public override void Close()
        {
            base.Close();
            this.m_DbConnection = new SqlConnection(this.m_DbConnection.ConnectionString);
        }

        #region Support Property & method
        /// <summary>
        /// 返回数据库类型枚举成员(DatabaseType)
        /// </summary>
        public override DatabaseType DatabaseType
        {
            get { return DatabaseType.MSSQLServer; }
        }

        private SqlConnection m_DbConnection;
        /// <summary>
        /// 返回数据库连接接口
        /// </summary>
        public override IDbConnection DbConnection
        {
            get
            {
                return m_DbConnection;
            }
        }

        private SqlTransaction trans;
        /// <summary>
        /// 开始一个事务
        /// </summary>
        /// <returns>数据库事务接口</returns>
        public override IDbTransaction BeginTransaction()
        {
            trans = m_DbConnection.BeginTransaction();
            return trans;
        }

        #endregion Support Property & method

        #region ExecuteNonQuery
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">SQL语句的参数集合</param>
        /// <param name="row">DataRow对象</param>
        /// <param name="sqlStruct">SqlStruct对象,封装Sql语句及参数</param>
        /// <returns>执行SQL语句影响的行数</returns>
        public override int ExecuteNonQuery(CommandType commandType, string commandText, QueryParameterCollection commandParameters, DataRow row, SqlStruct sqlStruct)
        {
            int tmpValue = 0;
            base.LogOutPut(new Func<int>(() =>
            {
                SqlCommand cmd = new SqlCommand();
                if (sqlStruct == null)
                    PrepareCommand(cmd, commandType, commandText, commandParameters);
                else
                {
                    for (int i = 0; i < sqlStruct.ParamFields.Length; i++)
                    {
                        if (sqlStruct.ParamFields[i].ParamName.IndexOf('_') != 1)
                            commandParameters.Add(sqlStruct.ParamFields[i].ParamName, row[sqlStruct.ParamFields[i].FieldName]);
                        else
                            commandParameters.Add(sqlStruct.ParamFields[i].ParamName, row[sqlStruct.ParamFields[i].FieldName, DataRowVersion.Original]);
                    }
                    PrepareCommand(cmd, CommandType.Text, sqlStruct.SqlString, commandParameters);
                }
                tmpValue = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return tmpValue;
            }), "ExecuteNonQuery", commandText, commandParameters);

            return tmpValue;
        }
        #endregion ExecuteNonQuery

        #region ExecuteDataSet
        /// <summary>
        /// 执行SQL语句,返回DataSet对象.
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">SQL语句的参数集合</param>
        /// <param name="ds">DataSet对象</param>
        /// <param name="tableName">DataSet中表的名称</param>
        /// <returns>DataSet对象</returns>
        public override DataSet ExecuteDataset(CommandType commandType, string commandText, QueryParameterCollection commandParameters, DataSet ds, string tableName)
        {
            base.LogOutPut(new Func<int>(() =>
            {

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, commandType, commandText, commandParameters);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                if (Object.Equals(tableName, null) || (tableName.Length < 1))
                    da.Fill(ds);
                else
                    da.Fill(ds, tableName);

                for (int j = 0; j < cmd.Parameters.Count; j++)
                {
                    if (cmd.Parameters[j].Direction == ParameterDirection.Output)
                        commandParameters[j].Value = cmd.Parameters[j].Value;
                }

                cmd.Parameters.Clear();
                //Console.WriteLine(ds.Tables.Count.ToString());
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0].Rows.Count;
                }
                return 0;
            }), "ExecuteDataset", commandText, commandParameters);
            return ds;
        }

        public override DataSet ExecuteDataset(string commandText, QueryParameterCollection commandParameters, DataSet ds, int startRecord, int maxRecords, string tableName)
        {
            base.LogOutPut(new Func<int>(() =>
            {

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, CommandType.Text, commandText, commandParameters);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                if (Object.Equals(tableName, null) || (tableName.Trim() == string.Empty))
                    da.Fill(ds);
                else
                    da.Fill(ds, startRecord, maxRecords, tableName);
                cmd.Parameters.Clear();
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0].Rows.Count;
                }
                return 0;
            }), "ExecuteDataset", commandText, commandParameters);
            return ds;
        }

        #endregion ExecuteDataSet

        #region ExecuteReader
        /// <summary>
        /// 执行SQL语句,返回IDataReader接口
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        /// <returns>IDataReader</returns>
        public override IDataReader ExecuteReader(CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            IDataReader dr = null;
            base.LogOutPut(new Func<int>(() =>
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, commandType, commandText, commandParameters);
                dr = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return -1;
            }), "ExecuteReader", commandText, commandParameters);

            return dr;
        }
        #endregion ExecuteReader

        #region ExecuteScalar
        /// <summary>
        /// 执行SQL语句,返回object接口
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        /// <returns>object</returns>
        public override object ExecuteScalar(CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            object tmpValue = null;

            base.LogOutPut(new Func<int>(() =>
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, commandType, commandText, commandParameters);
                tmpValue = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return -1;
            }), "ExecuteScalar", commandText, commandParameters);

            return tmpValue;
        }
        #endregion ExecuteScalar

        #region ExecuteXmlReader
        /// <summary>
        /// 执行SQL语句,返回XmlReader对象
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        /// <returns>XmlReader</returns>
        public override XmlReader ExecuteXmlReader(CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            XmlReader reader = null;
            base.LogOutPut(new Func<int>(() =>
            {

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, commandType, commandText, commandParameters);
                reader = cmd.ExecuteXmlReader();
                cmd.Parameters.Clear();
                return -1;
            }), "ExecuteXmlReader", commandText, commandParameters);

            return reader;
        }
        #endregion ExecuteXmlReader

        #endregion
        /// <summary>
        /// 处理SqlCommand对象,设置参数等信息.
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        private void PrepareCommand(SqlCommand cmd, CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            const string pattern = @"'\d{4}-(1[0-2]|[0]?\d)-(3[01]|[0-2]?\d)[-,\s*](2[0-3]|[0-1]?\d)[.,:]([0-5]\d|[0]?\d)[.,:]([0-5]\d|[0]?\d)'";
            if (Regex.IsMatch(commandText, pattern))
            {
                foreach (Match m in Regex.Matches(commandText, pattern))
                {
                    if (m.Value.IndexOf(':') > 0)
                    {
                        string regStr = m.Value.Replace(":", "##");
                        commandText = commandText.Replace(m.Value, regStr);
                    }
                }
            }
            commandText = commandText.ToFilterKeywordReplace(":", "@");
            commandText = commandText.ToFilterKeywordReplace("##", ":");
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            cmd.Connection = this.m_DbConnection;
            cmd.Transaction = trans;
            DbTypeEnum typeenum = new DbTypeEnum();
            if ((commandParameters != null) && (commandParameters.Count > 0))
            {
                for (int i = 0; i < commandParameters.Count; i++)
                {
                    cmd.Parameters.AddWithValue(commandParameters[i].ParameterName, commandParameters[i].Value ?? DBNull.Value);
                    cmd.Parameters[i].Direction = commandParameters[i].Direction;
                    //调用存储过程时需要指定参数的类型
                    if (cmd.CommandType == CommandType.StoredProcedure)
                        cmd.Parameters[i].SqlDbType = (SqlDbType)commandParameters[i].DbType;
                    //解决由于C#模式string 为NVARCHAR类型而数据库是varchar时。造成速度很慢
                    int type = typeenum.GetTypeEnum(cmd.Parameters[i].DbType.ToString());
                    if (type != 0)
                    {
                        cmd.Parameters[0].SqlDbType = (SqlDbType)type;
                    }
                }
            }
        }



        #region IDataStructure
        public override void FillingTables(List<TableInfo> info)
        {
            const string commandText = @"select table_name as TABLENAME, PKNAME
                                                from information_schema.tables a 
                                                LEFT JOIN (SELECT sysindexes.name as PKNAME,sysindexes.id FROM sysindexes,sysobjects 
                                                where 
                                                sysindexes.name=sysobjects.name   and xtype='PK') t on t.id  = object_id(a.table_name)
                                                where a.table_name != 'sysdiagrams' and a.table_type = 'BASE TABLE' 
                                                order by table_name";
            using(IDataReader dr = this.ExecuteReader(commandText))
            {
                while (dr.Read())
                {
                    info.Add(new TableInfo
                                 {
                                     TableName = dr["TABLENAME"].ToString(),
                                     PrimaryKeyName = dr["PKNAME"].ToString()
                                 });
                }
            }
        }

        public override void FillingColumns(List<ColumnInfo> info)
        {
            const string commandText = @"SELECT a.Table_name as TABLENAME,b.COLUMN_NAME as COLUMNNAME,b.DATA_TYPE  as TYPENAME,case when b.CHARACTER_MAXIMUM_LENGTH is null then b.numeric_precision else b.CHARACTER_MAXIMUM_LENGTH  end LENGTH,
                                         (
                                              select   count(1)  from   syscolumns  
                                              join   sysindexkeys on   syscolumns.id=sysindexkeys.id   and   syscolumns.colid=sysindexkeys.colid   
                                              join   sysindexes  on   syscolumns.id=sysindexes.id   and   sysindexkeys.indid=sysindexes.indid  
                                              join   sysobjects  on   sysindexes.name=sysobjects.name   and   sysobjects.xtype='PK'
                                              WHERE syscolumns.name=b.COLUMN_NAME AND     syscolumns.id=object_id(a.Table_name )
                                          ) PRIMARYKEY ,
                                          (select count(1)
                                              from syscolumns col,sysforeignkeys f
                                              where f.fkeyid=col.id
                                            and f.fkey=col.colid and col.name=b.COLUMN_NAME
                                            and f.constid in
                                                (select distinct(id)
                                                 from sysobjects
                                                 where xtype='F' and OBJECT_NAME(parent_obj)=a.Table_name)
                                          ) FOREIGNKEY,
                                          case b.IS_NULLABLE when 'NO' then 'Not Null' when 'YES' then 'Null' end ISNULL  ,
                                          case when numeric_scale is null then 0 else numeric_scale end DATA_SCALE,ORDINAL_POSITION AS COLUMNINDEX,
                                          COLUMN_DEFAULT as DEFAULTVALUE
                                         FROM INFORMATION_SCHEMA.TABLES  a   
                                         LEFT JOIN INFORMATION_SCHEMA.COLUMNS b    
                                              ON a.TABLE_NAME = b.TABLE_NAME
                                              order by a.TABLE_NAME,PRIMARYKEY desc,Foreignkey desc,COLUMN_NAME  ";

            using (IDataReader dr = this.ExecuteReader(commandText))
            {
                while (dr.Read())
                {
                    info.Add(new ColumnInfo
                                 {
                                     TableName = dr["TABLENAME"].ToString(),
                                     ColumnName = dr["COLUMNNAME"].ToString(),
                                     DefaultValue = dr["DEFAULTVALUE"].ToString(),
                                     TypeName = dr["TYPENAME"].ToString(),
                                     Length = dr["LENGTH"].ToString(),
                                     IsPrimaryKey =
                                         dr["PRIMARYKEY"].ToString() != string.Empty &&
                                         dr["PRIMARYKEY"].ToString() != "0",
                                     IsNull = dr["ISNULL"].ToString() == "Null",
                                     DataScale = dr["DATA_SCALE"].ToString(),
                                     ColumnIndex = Convert.ToInt32(dr["COLUMNINDEX"].ToString()),
                                     IsForeignKey =
                                         dr["FOREIGNKEY"].ToString() != string.Empty &&
                                         dr["FOREIGNKEY"].ToString() != "0"
                                 });
                }
            }
        }

        public override void FillingIndexs(List<IndexInfo> info)
        {
            const string commandText = @" select a.name  as INDNAME,c.name as TABNAME,d.name  as COLNAMES,case indexkey_property(c.id,b.indid,b.keyno,'isdescending') 
 		                                    when 1 then 'DESC' when 0 then 'ASC' end as Sequence,INDEXPROPERTY(c.id,a.name,'IsClustered') as remark,
                                            INDEXPROPERTY(c.id,a.name,'IsUnique') as IsUnique
                                            from sysindexes a 
 	                                        join sysindexkeys b on a.id=b.id and a.indid=b.indid 
 	                                        join sysobjects c on b.id=c.id and c.xtype='U' and c.status>=0 
 	                                        join syscolumns d on b.id=d.id and b.colid=d.colid 
 	                                        left join sysobjects e on b.indid=e.id and e.xtype='UQ' 
                                            order by c.name,a.name ";
            using (IDataReader dr = this.ExecuteReader(commandText))
            {
                while (dr.Read())
                {
                    string columns = dr["COLNAMES"].ToString();
                    for (int i = 0; i < columns.Length; i++)
                    {
                        IndexInfo entity = new IndexInfo
                                               {
                                                   Sequence = columns[i] == '+' ? "ASC" : "DESC"
                                               };
                        int index = columns.IndexOf('+', i + 1);
                        if (index < 0)
                        {
                            index = columns.IndexOf('-', i + 1);
                        }
                        if (index > 0)
                        {
                            entity.ColumnName = columns.Substring(i + 1, index - (i + 1));
                            i = index - 1;
                        }
                        else
                        {
                            entity.ColumnName = columns.Substring(i + 1);
                            i = columns.Length;
                        }
                        entity.TableName = dr["TABNAME"].ToString();
                        entity.IndexName = dr["INDNAME"].ToString();
                        entity.IsUnique = dr["IsUnique"].ToString() != string.Empty && dr["IsUnique"].ToString() == "1";
                        info.Add(entity);
                    }
                }
            }
        }

        public override void FillingRelations(List<RelationshipInfo> info)
        {
            const string commandText = @"SELECT OBJECT_NAME(constid) as refkeyname,OBJECT_NAME(c1.id) as tabname,c1.name as colname,
                                            OBJECT_NAME(c2.id) as reftabname,c2.name as refcolname,
                                            ObjectProperty(f.constid,'CnstIsUpdateCascade') as CascadingUpdate,ObjectProperty(f.constid,'CnstIsDeleteCascade') as CascadingDelete 
                                            FROM sysforeignkeys f,syscolumns c1 ,syscolumns c2
                                            where f.fkey=c1.colid and f.fkeyid = c1.id 
                                            and f.rkey=c2.colid and f.rkeyid = c2.id";

            using (IDataReader dr = this.ExecuteReader(commandText))
            {
                while (dr.Read())
                {
                    info.Add(new RelationshipInfo
                                 {
                                     TableName = dr["TABNAME"].ToString(),
                                     RefKeyName = dr["REFKEYNAME"].ToString().Trim(),
                                     ColumnName = dr["COLNAME"].ToString().Trim(),
                                     RelationTableName = dr["REFTABNAME"].ToString().Trim(),
                                     RelationColumnName = dr["REFCOLNAME"].ToString().Trim(),
                                     IsCascadingDelete =
                                         dr["CascadingDelete"].ToString() != string.Empty &&
                                         dr["CascadingDelete"].ToString() == "1",
                                     IsCascadingUpdate =
                                         dr["CascadingUpdate"].ToString() != string.Empty &&
                                         dr["CascadingUpdate"].ToString() == "1"
                                 });
                }
            }
        }

        public override void FillingViews(List<ViewInfo> info)
        {

            const string commandText = @" SELECT
                      object_name(sysobjects.id) AS VIEWNAME,
                      text AS CONTENT
                  FROM
                      sysobjects
                      LEFT JOIN syscomments on syscomments.id = sysobjects.id
                  WHERE
                      type = N'V'
                      AND permissions(sysobjects.id) & 4096 <> 0
                      AND ObjectProperty(sysobjects.id, N'IsMSShipped') = 0
                      AND NOT EXISTS (SELECT * FROM sys.extended_properties WHERE major_id = sysobjects.id AND name = 'microsoft_database_tools_support' AND value = 1)
                  ORDER BY object_name(sysobjects.id)";

            using (IDataReader dr = this.ExecuteReader(commandText))
            {
                while (dr.Read())
                {
                    info.Add(new ViewInfo
                                 {
                                     ViewName = dr["VIEWNAME"].ToString(),
                                     Content = dr["CONTENT"].ToString()
                                 });
                }
            }
        }

        public override void FillingProcs(List<ProcInfo> info)
        {
            const string commandText = "select name as PROCNAME,o.id as PROCID ,text as CONTENT,(select count(0) " +
                                       "from syscolumns where id = o.id) as PARAMCOUNT from dbo.syscomments c, dbo.sysobjects o " +
                                       "where o.id = c.id and o.type = 'p' and name not like 'sp_%' order by name";

            using (IDataReader dr = this.ExecuteReader(commandText))
            {
                while (dr.Read())
                {
                    info.Add(new ProcInfo
                                 {
                                     ProcId = dr["PROCID"].ToString(),
                                     ProcName = dr["PROCNAME"].ToString(),
                                     Content = dr["CONTENT"].ToString()
                                 });
                }
            }
        }

        public override void FillingProcParams(List<ProcParamInfo> info)
        {
            const string commandText = @"SELECT o.id as PROCID,c.name as PARMNAME,c.prec as LENGTH,b.name as TYPENAME,c.SCALE,c.colid as 'INDEX',
                                                case c.isoutparam  when 0 then 'IN' when 1 then 'OUT' end STATUS
                                                FROM SysColumns c,sysobjects o,systypes b 
                                                where c.id = o.id and  o.type = 'p' and c.xtype=b.xtype and b.status=0
                                                order by o.name,c.colid";
            using (IDataReader dr = this.ExecuteReader(commandText))
            {
                while (dr.Read())
                {
                    info.Add(new ProcParamInfo
                                 {
                                     ProcId = dr["PROCID"].ToString(),
                                     ProcParamName = dr["PARMNAME"].ToString(),
                                     TypeName = dr["TYPENAME"].ToString(),
                                     Length = dr["LENGTH"].ToString(),
                                     State = dr["STATUS"].ToString(),
                                     ParamIndex = Convert.ToInt32(dr["INDEX"].ToString()),
                                     DataScale = dr["SCALE"].ToString(),
                                 });
                }
            }
        }

        public override void FillingTypeInfos(List<TypeNameInfos> info)
        {
            const string commandText = @"SELECT NAME,LENGTH,XSCALE FROM SysTypes
                                        where name != 'sysname'";
            using (IDataReader dr = this.ExecuteReader(commandText))
            {
                while (dr.Read())
                {
                    info.Add(new TypeNameInfos
                    {
                        TypeName = dr["NAME"].ToString(),
                        Length = Convert.ToInt32(dr["LENGTH"].ToString()),
                        Scale = Convert.ToInt32(dr["XSCALE"].ToString())
                    });
                }
            }
        }
        #endregion

      
    }
}
